In this exercise, you will learn how to use ADO.NET, ODBC, OLEDB and LINQ to SQL technologies to connect to your SQL Azure database and perform some simple T-SQL operations. In addition, you will see how to connect to the database from other technologies like Java and PHP.
Using Microsoft Technologies, you will see that the way in which you interact with your SQL Azure database from your applications is the same as a traditional SQL database. The main differences between the technologies lie in the type of connection and the connection strings used to connect to SQL Azure. After the connection is established, you can then use the appropriate inheritor of the ‘DbCommand’ to issue your commands to SQL Azure.
Task 1 – Opening the Begin Solution and Exploring the Common Functionalities
You will test the different Microsoft technologies connecting to SQL Azure and performing some tasks against a new table. To avoid spending time implementing logic that creates, inserts, queries and deletes a table, this exercise provides a begin solution that implements these common functionalities. This allows you to focus on learning how to connect to SQL Azure and explore the differences between the proposed technologies.
In this task, you will open the ConnectDemoApp solution and explore the SQLAzureConnectionDemo class. During the exercise, you will inherit from this class for each different implementation of a data access technology.
- Open Microsoft Visual Studio 2010 from Start | All Programs | Microsoft Visual Studio 2010 | Microsoft Visual Studio 2010.
- Open the begin solution provided for this exercise. To do this, from the File menu, choose Open Project. In the Open Project dialog, navigate to Ex4-ConnectingViaClientLibraries\begin inside the Source folder of this lab. Select the folder for the language of your preference (C# or VB), and then open the solution ConnectDemoApp.sln inside the ConnectDemoApp folder. A solution with the following structure should open.
Figure 48
Connect Demo App solution’s structure (C#)
Figure 49
Connect Demo App solution’s structure (Visual Basic) - As mentioned before, you will create a class per technology inheriting from the SQLAzureConnectionDemo abstract class. This class provides common functionality to perform basic operations against SQL Azure using the provider that you implement in the derived class. The table below explains each of the methods in this class to understand how it works and determine which methods you need to implement in the derived classes:
Method
Type
Description
Class Constructor
Sets the connection property based on the result of the CreateConnection abstract method that will be implemented on the derived class.
CreateConnection
Abstract
A derived class implements this method in order to create the connection according to the underlying technology.
CreateCommand
Abstract
A derived class implements this method to create a command according to the underlying technology.
GetServerName
Returns the server name from the data source. It is a common task required to create the connection to the database.
ConnectToSQLAzureDemo
Executes the demo flow against the SQL Azure Database. It gets a command from the derived class using the CreateCommand method and then executes the Execute* methods to create, fill, query and delete a demo table.
ExecuteCreateDemoTableStatement
Executes a create table statement to create the “DemoTable” table.
ExecuteInsertTestDataStatement
Executes an insert statement against the “DemoTable” table.
ExecuteReadInsertedTestData
Executes a select statement trying to retrieve the data inserted by the previous method and calls the ReadData method to show it in the Console.
ReadData
Reads the data retrieved from the table and displays it in the Console.
ExecuteDropDemoTable
Executes a delete statement removing the “DemoTable” table from the SQL Azure database.
Notice that you will only have to override the CreateConnection and CreateCommand methods on the implementation of each technology to create a connection to SQL Azure successfully.
Task 2 – Connecting to SQL Azure Using ADO.NET
- In this task, you will create a class that inherits from the SQLAzureConnectionDemo class and implements the methods to connect to SQL Azure using ADO.NET.
- Add a new class to the project named AdoConnectionDemo. To do this, right-click the ConnectDemoApp project in Solution Explorer and select Add | Class. In the Add New Item dialog, make sure that you select the Class template and set the name to AdoConnectionDemo.cs or AdoConnectionDemo.vb depending on the language of your project.
- Make sure that you have the following namespace directives at the top of the file:
C# 
Copy Code
using System.Data.Common; using System.Data.SqlClient;
Visual Basic Copy Code
Imports System.Data.Common Imports System.Data.SqlClient
- Update the class definition to make it public and to inherit from SQLAzureConnectionDemo. The final implementation should look like the following:
Note:In Visual Basic, the template for a new class already declares the class as Public. C# Copy Code
public class AdoConnectionDemo : SQLAzureConnectionDemo { }Visual Basic Copy Code
Public Class AdoConnectionDemo Inherits SQLAzureConnectionDemo End Class
- Implement the class constructor to retrieve the connection information and pass it as parameters to the base class constructor:
(Code Snippet – Intro to SQL Azure – Ex4 ADO constructor – C#)
C# Copy Code
public AdoConnectionDemo(string userName, string password, string dataSource, string databaseName) : base(userName, password, dataSource, databaseName) { }(Code Snippet – Intro to SQL Azure – Ex4 ADO constructor – VB)
Visual Basic Copy Code
Public Sub New(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) MyBase.New(userName, password, dataSource, databaseName) End Sub
- The SQLAzureConnectionDemo class delegates the connection construction to the derived class. Override the CreateConnection method to create a SqlConnection in your AdoConnectionDemo class:
(Code Snippet – Intro to SQL Azure – Ex4 ADO CreateConnection – C#)
C# Copy Code
protected override DbConnection CreateConnection(string userName, string password, string dataSource, string databaseName) { return new SqlConnection(CreateAdoConnectionString(userName, password, dataSource, databaseName)); }(Code Snippet – Intro to SQL Azure – Ex4 ADO CreateConnection – VB)
Visual Basic Copy Code
Protected Overrides Function CreateConnection(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As DbConnection Return New SqlConnection(CreateAdoConnectionString(userName, password, dataSource, databaseName)) End Function
- Implement the CreateAdoConnectionString method used by the CreateConnection method. This method is responsible for building up the connection string for the ADO.NET Connection, which takes advantage of the SqlConnectionStringBuilder class in the underlying implementation.
(Code Snippet – Intro to SQL Azure – Ex4 ADO CreateAdoConnectionString method – C#)
C# Copy Code
private string CreateAdoConnectionString(string userName, string password, string dataSource, string databaseName) { // create a new instance of the SQLConnectionStringBuilder SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder { DataSource = dataSource, InitialCatalog = databaseName, Encrypt = true, TrustServerCertificate = false, UserID = userName, Password = password, }; return connectionStringBuilder.ToString(); }(Code Snippet – Intro to SQL Azure – Ex4 ADO CreateAdoConnectionString method – VB)
Visual Basic Copy Code
Private Function CreateAdoConnectionString(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As String ' create a new instance of the SQLConnectionStringBuilder Dim connectionStringBuilder As SqlConnectionStringBuilder = New SqlConnectionStringBuilder With {.DataSource = dataSource, .InitialCatalog = databaseName, .Encrypt = True, .TrustServerCertificate = False, .UserID = userName, .Password = password} Return connectionStringBuilder.ToString() End Function - Override the CreateCommand method to create an ADO.NET command. Remember that this abstract method is called in the parent class to get the connection and execute the different SQL statement samples.
(Code Snippet – Intro to SQL Azure – Ex4 ADO CreateCommand method – C#)
C# Copy Code
protected override DbCommand CreateCommand(DbConnection connection) { return new SqlCommand() { Connection = connection as SqlConnection }; }(Code Snippet – Intro to SQL Azure – Ex4 ADO CreateCommand method – VB)
Visual Basic Copy Code
Protected Overrides Function CreateCommand(ByVal connection As DbConnection) As DbCommand Return New SqlCommand() With {.Connection = TryCast(connection, SqlConnection)} End FunctionThat is all the code required to use an ADO.NET connection. Now you will include some code on the Program.cs or Module1.vb file (depending on the language of your project) to test the connection and see how the different operations work.
- Open the Program.cs (for Visual C# projects) or Module1.vb (for Visual Basic projects) file double-clicking it in the Solution Explorer inside the ConnectDemoApp project.
- Implement the logic to create an instance of the AdoConnectionDemo class and execute the demo against SQL Azure.
(Code Snippet – Intro to SQL Azure – Ex4 ADO demo implementation – C#)
C# Copy Code
static void Main(string[] args) { //Invoke the ADO.NET connection demo Console.WriteLine("Starting the ADO.NET Connection Demo..."); AdoConnectionDemo demo1 = new AdoConnectionDemo(userName, password, datasource, databaseName); demo1.ConnectToSQLAzureDemo(); Console.WriteLine("Demo Complete... Press any key"); Console.ReadKey(); }(Code Snippet – Intro to SQL Azure – Ex4 ADO demo implementation – VB)
Visual Basic Copy Code
Sub Main() ' Invoke the ADO.NET connection demo Console.WriteLine("Starting the ADO.NET Connection Demo...") Dim demo1 = New AdoConnectionDemo(_userName, _password, _datasource, _databaseName) demo1.ConnectToSQLAzureDemo() Console.WriteLine("Demo Complete... Press any key") Console.ReadKey() End Sub - Locate the member variables declared immediately above method Main and update the placeholders with the connection information for your SQL Azure account.
Figure 50
Configuring connection parameters for SQL Azure (C#)
Figure 51
Configuring connection parameters for SQL Azure (Visual Basic) - Run the application by pressing F5. You should see the following output in a console window.
Figure 52
Expected output from the ADO.NET connection demo
Task 3 – Connecting to SQL Azure Using ODBC
- In this task, you will create a class that inherits from the SQLAzureConnectionDemo class and implement the methods for connecting to SQL Azure using ODBC.
- Add a new class to the project named OdbcConnectionDemo. To do this, right-click the ConnectDemoApp project in Solution Explorer and select Add | Class. In the Add New Item dialog, make sure that you select the Class template and set the name to OdbcConnectionDemo.cs or OdbcConnectionDemo.vb depending on the language of your project.
- Make sure that you have the following namespace directives at the top of the file:
C# Copy Code
using System.Data.Common; using System.Data.Odbc;
Visual Basic Copy Code
Imports System.Data.Common Imports System.Data.Odbc
- Update the class definition to make it public and to inherit from SQLAzureConnectionDemo. The final implementation should look like the following:Note:
In Visual Basic, the template for a new class already declares the class as Public. C# Copy Code
public class OdbcConnectionDemo :SQLAzureConnectionDemo { }Visual Basic Copy Code
Public Class OdbcConnectionDemo Inherits SQLAzureConnectionDemo End Class
- Implement the class constructor to get the connection information and pass it as parameters to the base class constructor:
(Code Snippet – Intro to SQL Azure – Ex4 ODBC constructor – C#)
C# Copy Code
public OdbcConnectionDemo(string userName, string password, string dataSource, string databaseName): base (userName, password, dataSource, databaseName) { }(Code Snippet – Intro to SQL Azure – Ex4 ODBC constructor – VB)
Visual Basic Copy Code
Public Sub New(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) MyBase.New(userName, password, dataSource, databaseName) End Sub
- Override the CreateConnection method to create an OdbcConnection in your OdbcConnectionDemo class:
(Code Snippet – Intro to SQL Azure – Ex4 ODBC CreateConnection – C#)
C# Copy Code
protected override DbConnection CreateConnection(string userName, string password, string dataSource, string databaseName) { return new OdbcConnection(CreateOdbcConnectionString(userName, password, dataSource, databaseName)); }(Code Snippet – Intro to SQL Azure – Ex4 ODBC CreateConnection – VB)
Visual Basic Copy Code
Protected Overrides Function CreateConnection(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As DbConnection Return New OdbcConnection(CreateOdbcConnectionString(userName, password, dataSource, databaseName)) End Function
- Implement the CreateOdbcConnectionString method used by the CreateConnection method. This method is responsible for building up the ODBC Drivers connection string. The proposed implementation is using SQL Server Native Client 10.0 as its driver. You can specify any other ODBC driver of your preference here.
(Code Snippet – Intro to SQL Azure – Ex4 ODBC CreateOdbcConnectionString method – C#)
C# Copy Code
private string CreateOdbcConnectionString(string userName, string password, string dataSource, string databaseName) { string serverName = GetServerName(dataSource); OdbcConnectionStringBuilder connectionStringBuilder = new OdbcConnectionStringBuilder { Driver = "SQL Server Native Client 10.0", }; connectionStringBuilder["Server"] = "tcp:" + dataSource; connectionStringBuilder["Database"] = databaseName; connectionStringBuilder["Uid"] = userName + "@" + serverName; connectionStringBuilder["Pwd"] = password; return connectionStringBuilder.ConnectionString; }(Code Snippet – Intro to SQL Azure – Ex4 ODBC CreateOdbcConnectionString method – VB)
Visual Basic Copy Code
Private Function CreateOdbcConnectionString(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As String Dim serverName As String = GetServerName(dataSource) Dim connectionStringBuilder As OdbcConnectionStringBuilder = New OdbcConnectionStringBuilder With {.Driver = "SQL Server Native Client 10.0"} connectionStringBuilder("Server") = "tcp:" & dataSource connectionStringBuilder("Database") = databaseName connectionStringBuilder("Uid") = userName & "@" & serverName connectionStringBuilder("Pwd") = password Return connectionStringBuilder.ConnectionString End Function - Override the CreateCommand method to create an OdbcCommand. Remember that this abstract method is called in the parent class to get the connection and execute the different SQL statement samples.
(Code Snippet – Intro to SQL Azure – Ex4 ODBC CreateCommand method – C#)
C# Copy Code
protected override DbCommand CreateCommand(DbConnection connection) { return new OdbcCommand() { Connection = connection as OdbcConnection }; }(Code Snippet – Intro to SQL Azure – Ex4 ODBC CreateCommand method – VB)
Visual Basic Copy Code
Protected Overrides Function CreateCommand(ByVal connection As DbConnection) As DbCommand Return New OdbcCommand() With {.Connection = TryCast(connection, OdbcConnection)} End FunctionThat is the specific code required to use an ODBC connection. Now you will include some code in the Program.cs or Module1.vb file (depending on the language of your project) to test the connection and see how the different operations work.
- Open the Program.cs (for Visual C# projects) or Module1.vb (for Visual Basic projects) file double-clicking it in the SolutionExplorer inside the ConnectDemoApp project.
- In method Main, implement the logic to create a new instance of the OdbcConnectionDemo class and execute the demo against SQL Azure. You can add or replace the code from the previous tasks based on whether you want to test all the technologies at once or only this one.
(Code Snippet – Intro to SQL Azure – Ex4 ODBC demo implementation – C#)
C# Copy Code
static void Main(string[] args) { //... //Invoke the ODBC connection demo Console.WriteLine("Starting the ODBC Connection Demo..."); OdbcConnectionDemo demo2 = new OdbcConnectionDemo(userName, password, datasource, databaseName); demo2.ConnectToSQLAzureDemo(); Console.WriteLine("Demo Complete... Press any key"); Console.ReadKey(); }(Code Snippet – Intro to SQL Azure – Ex4 ODBC demo implementation – VB)
Visual Basic Copy Code
Sub Main() ' ... ' Invoke the ODBC connection demo Console.WriteLine("Starting the ODBC Connection Demo...") Dim demo2 = New OdbcConnectionDemo(_userName, _password, _datasource, _databaseName) demo2.ConnectToSQLAzureDemo() Console.WriteLine("Demo Complete... Press any key") Console.ReadKey() End Sub - If you have not done so before, update the value of the member variables located immediately above method Main by replacing the placeholders with the connection information for your SQL Azure account.
- Run the application by pressing F5. You should see the following output in a console window.
Figure 53
Expected output from the ODBC connection demo
Task 4 – Connecting to SQL Azure Using OLEDB
- In this task, you will create a class that inherits from the SQLAzureConnectionDemo class and implements the methods for connecting to SQL Azure using OLEDB.
- Add a new class to the project named OleDbConnectionDemo. To do this, right-click the ConnectDemoApp project in SolutionExplorer and select Add | Class. In the AddNewItem dialog, make sure to select the Class template and set the name to OleDbConnectionDemo.cs or OleDbConnectionDemo.vb depending on the language of your project.
- Make sure that you have the following namespace directives at the top of the file:
C# Copy Code
using System.Data.Common; using System.Data.OleDb;
Visual Basic Copy Code
Imports System.Data.Common Imports System.Data.OleDb
- Update the class definition to make it public and to inherit from SQLAzureConnectionDemo. It should look like the following:Note:
In Visual Basic, the template for a new class already declares the class as Public. C# Copy Code
public class OleDbConnectionDemo : SQLAzureConnectionDemo { }Visual Basic Copy Code
Public Class OleDbConnectionDemo Inherits SQLAzureConnectionDemo End Class
- Implement the class constructor to get the connection information and pass it as parameters to the base class constructor:
(Code Snippet – Intro to SQL Azure – Ex4 OLEDB constructor – C#)
C# Copy Code
public OleDbConnectionDemo(string userName, string password, string dataSource, string databaseName) : base(userName, password, dataSource, databaseName) { }(Code Snippet – Intro to SQL Azure – Ex4 OLEDB constructor – VB)
Visual Basic Copy Code
Public Sub New(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) MyBase.New(userName, password, dataSource, databaseName) End Sub
- Override the CreateConnection method to create an OleDbConnection in your OleDbConnectionDemo class:
(Code Snippet – Intro to SQL Azure – Ex4 OLEDB CreateConnection – C#)
C# Copy Code
protected override DbConnection CreateConnection(string userName, string password, string dataSource, string databaseName) { return new OleDbConnection(CreateOleDBConnectionString(userName, password, dataSource, databaseName)); }(Code Snippet – Intro to SQL Azure – Ex4 OLEDB CreateConnection – VB)
Visual Basic Copy Code
Protected Overrides Function CreateConnection(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As DbConnection Return New OleDbConnection(CreateOleDBConnectionString(userName, password, dataSource, databaseName)) End Function
- Implement the CreateOleDbConnectionString method used by the CreateConnection method. This method is responsible for building up the connection string used to create the connection to SQL Azure using OLEDB.
(Code Snippet – Intro to SQL Azure – Ex4 OLEDB CreateOleDbConnectionString method – C#)
C# Copy Code
private string CreateOleDBConnectionString(string userName, string password, string dataSource, string databaseName) { string serverName = GetServerName(dataSource); OleDbConnectionStringBuilder connectionStringBuilder = new OleDbConnectionStringBuilder { Provider = "SQLOLEDB", DataSource = dataSource, }; connectionStringBuilder["Initial Catalog"] = databaseName; connectionStringBuilder["UId"] = userName + "@" + serverName; connectionStringBuilder["Pwd"] = password; return connectionStringBuilder.ConnectionString; }(Code Snippet – Intro to SQL Azure – Ex4 OLEDB CreateOleDbConnectionString method – VB)
Visual Basic Copy Code
Private Function CreateOleDBConnectionString(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As String Dim serverName As String = GetServerName(dataSource) Dim connectionStringBuilder As OleDbConnectionStringBuilder = New OleDbConnectionStringBuilder With {.Provider = "SQLOLEDB", .DataSource = dataSource} connectionStringBuilder("Initial Catalog") = databaseName connectionStringBuilder("UId") = userName & "@" & serverName connectionStringBuilder("Pwd") = password Return connectionStringBuilder.ConnectionString End Function - Override the CreateCommand method to create an OleDbCommand. Remember that this abstract method is called in the parent class to get the connection and execute the different SQL statement samples.
(Code Snippet – Intro to SQL Azure – Ex4 OLEDB CreateCommand method – C#)
C# Copy Code
protected override DbCommand CreateCommand(DbConnection connection) { return new OleDbCommand() { Connection = connection as OleDbConnection }; }(Code Snippet – Intro to SQL Azure – Ex4 OLEDB CreateCommand method – VB)
Visual Basic Copy Code
Protected Overrides Function CreateCommand(ByVal connection As DbConnection) As DbCommand Return New OleDbCommand() With {.Connection = TryCast(connection, OleDbConnection)} End FunctionThat is the specific code required to use an OLEDB connection. Now you will include some code in the Program.cs or Module1.vb file (depending on the language of your project) to test the connection and see how the different operations work.
- Open the Program.cs (for Visual C# projects) or Module1.vb (for Visual Basic projects) file double-clicking it in the SolutionExplorer inside the ConnectDemoApp project.
- In method Main, implement the logic to create an instance of the OleDbConnectionDemo class and execute the demo against SQL Azure. You can add or replace the code from the previous tasks based on whether you want to test all the technologies at once or only this one.
(Code Snippet – Intro to SQL Azure – Ex4 OLEDB demo implementation – C#)
C# Copy Code
static void Main(string[] args) { //... //Invoke the OleDB connection demo Console.WriteLine("Starting the OLEDB Connection Demo…"); OleDbConnectionDemo demo3 = new OleDbConnectionDemo(userName, password, datasource, databaseName); demo3.ConnectToSQLAzureDemo(); Console.WriteLine("Demo Complete... Press any key"); Console.ReadKey(); }(Code Snippet – Intro to SQL Azure – Ex4 OLEDB demo implementation – VB)
Visual Basic Copy Code
Sub Main() ' ... ' Invoke the OleDB connection demo Console.WriteLine("Starting the OLEDB Connection Demo...") Dim demo3 = New OleDbConnectionDemo(_userName, _password, _datasource, _databaseName) demo3.ConnectToSQLAzureDemo() Console.WriteLine("Demo Complete... Press any key") Console.ReadKey() End Sub - If you have not done so before, update the value of the member variables located immediately above method Main by replacing the placeholders with the connection information for your SQL Azure account.
- Run the application by pressing F5. You should see the following output in a console window.
Figure 54
Expected output from the OLEDB connection demo
Task 5 – Connecting to SQL Azure Using Entity Framework
You have connected in three different ways to the database on SQL Azure. The last technology that you are going to try will be Entity Framework. You will notice that the class implementation for this demo will not inherit from the SQLAzureConnectionDemo class because when using Entity Framework, you do not have to manage Connections and Commands; those are administered by the underlying technology and you do not have to worry about them.
- Open the App.config file and change the connection string to point to your SQL Azure Database, and to connect using the test user created earlier in this lab.Note:
This step is required because Entity Framework gets the connection settings from the configuration file. - Add a new ADO.NET Entity Data Model to the project named HoLModel. To do this, right-click on the ConnectDemoApp project in the Solution Explorer. Select Add | New Item. In the Add New Item dialog, make sure you select ADO.NET Entity Data Model template and then set the name to HoLModel.edmx.
Figure 55
Adding EF model - In the Entity Data Model Wizard select Generate from database and click Next.
Figure 56
Choosing model contents - In the Choose Your Data Connection step, select Yes, include sensitive data in the connection string and leave AdventureWorksLTConnectionString as data connection and HolTestDBEntities as entity connection settings name.
Figure 57
Choosing model contents - In the Choose Your Database Objects step, select all database objects and click Finish.
Figure 58
Choosing database objects - Once created the Model is shown.
Figure 59
EF model created - Add a new class to the project named EFConnectionDemo. To do this, right-click the ConnectDemoApp project in SolutionExplorer and select Add | Class. In the AddNewItem dialog, make sure you select the Class template and then set the name to EFConnectionDemo.cs or EFConnectionDemo.vb based on the language of your preference.
- Update the class definition to make it public. It should look like the following:
C# Copy Code
public class EFConnectionDemo { }Note:If you are using Visual Basic, skip this step. Classes in Visual Basic are Public by Default. - Only for C#, make sure that you have the following namespace directives at the top of the class:
C# Copy Code
using System; using System.Linq;
- Add the following method to the EFConnectionDemo class. This retrieves from the database all the company names and prints them to the console. To do that, it takes advantage of the EF HolTestDBEntities class.
(Code Snippet – Intro to SQL Azure – Ex4 EF ConnectToSQLAzure method – C#)
C# Copy Code
/// <summary> /// HolTestDbEntities takes care of handling your transactions for you /// leaving you free use Linq to extract information stores up in the cloud /// </summary> public void ConnectToSQLAzureDemo() { HolTestDbEntities context = new HolTestDbEntities(); IQueryable<string> companyNames = from customer in context.Customers where customer.CustomerID < 20 select customer.CompanyName; foreach (var company in companyNames) { Console.WriteLine(company); } }(Code Snippet – Intro to SQL Azure – Ex4 EF ConnectToSQLAzure method – VB)
Visual Basic Copy Code
''' <summary> ''' HolTestDBEntities takes care of handling your transactions for you ''' leaving you free you use Linq to extraxt information stored up in the cloud. ''' </summary> Public Sub ConnectToSQLAzureDemo() Dim context As New HolTestDBEntities() ' get all company names Dim companyNames As IQueryable(Of String) = From customer In context.Customers _ Where customer.CustomerID < 20 _ Select customer.CompanyName ' display these all on the console For Each company As String In companyNames Console.WriteLine(company) Next company End Sub - Add the following code to invoke the LINQ to SQL demo in method Main of the Program.cs file (for Visual C# projects) or Module1.vb (for Visual Basic projects) file. You can add or replace the code from the previous tasks depending on whether you want to test all the technologies at once or only this one.
(Code Snippet – Intro to SQL Azure – Ex4 EF demo implementation – C#)
C# Copy Code
static void Main(string[] args) { //... //Invoke the Entity Framework connection demo Console.WriteLine("Starting the Entity Framework Connection Demo..."); EFConnectionDemo demo4 = new EFConnectionDemo(); demo4.ConnectToSQLAzureDemo(); Console.WriteLine("Demo Complete... Press any key"); Console.ReadKey(); }(Code Snippet – Intro to SQL Azure – Ex4 EF demo implementation – VB)
Visual Basic Copy Code
Sub Main() ' ... ' Invoke the Entity Framework connection demo Console.WriteLine("Starting the Entity Framework Connection Demo...") Dim demo4 = New EFConnectionDemo() demo4.ConnectToSQLAzureDemo() Console.WriteLine("Demo Complete... Press any key") Console.ReadKey() End Sub - Press F5 to run your application. You should see a long list of company names. These are retrieved from your database on the SQL Azure Server using LINQ to SQL.
Figure 60
Expected output from the EF connection demo
Task 6 – Connecting to SQL Azure via Non-Microsoft Technologies
It is trivial to connect to SQL Azure using non-windows technologies.
The following PHP version takes on a pattern that you should be familiar with from the previous task. It uses the SQL Server Native Client ODBC driver to establish a connection.
| PHP |
Copy Code
|
|---|---|
<?php
$host = "server.database.windows.net";
$dbname = "database";
$dbuser = "user@server";
$dbpwd = "password";
$driver = "{SQL Server Native Client 10.0}";
// Build connection string
$dsn="Driver=$driver;Server=$host;Database=$dbname;Encrypt=true;TrustServerCertificate=true";
if (!($conn = @odbc_connect($dsn, $dbuser, $dbpwd))) {
die("Connection error: " . odbc_errormsg());
}
// Got a connection, do what you will
// Free the connection
@odbc_close($conn);
?>
|
|
Connecting to SQL Azure using JDBC is also trivial. Refer to the following code.
| JAVA |
Copy Code
|
|---|---|
// Build a connection string
String connectionUrl= "jdbc:sqlserver://server.database.windows.net;" +
"database=mydatabase;encrypt=true;user=user@server;password=*****";
// Next, make the sure the SQL Server Driver is loaded.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Then attempt to get a connection. This will null or throw if we can't get a connection.
Connection sqlConn = DriverManager.getConnection(connectionUrl);
if (sqlConn == null)
{
System.out.println("Unable to obtain connection. exiting");
System.exit(1);
}
// Got a connection, do what you will
// Free the connection
sqlConn.close();
|
|
